/** 
Copyright (c) 2010, Sheikh Abdul Wahid Ahmed
Details @ http://extremecodeworld.codeplex.com/license
**/

using System;
using EXtremecode.Utils;
using System.Data;
using System.Data.SqlClient;
using EXtremecode.DataAccessLayer; 
using System.Collections;
using System.Text;
using EXtremecode.Common;
using System.Data.Common;
using System.Data.OleDb;


namespace EXtremecode.DataAccessLayer
{
	/// <summary>
	/// Summary description for SQLServerDataAccessor.
	/// </summary>
	public class SQLServerDataAccessor:DataAccessor
	{
		#region SYSTEM QUERIES
		internal override string QUERY_Table 
		{ 
			get 
			{
				return	"SELECT Name  FROM  sysobjects WHERE (xtype = 'u')";
			}
		}
        internal override string QUERY_View 
		{
			get 
			{
				return "SELECT  Name FROM sysobjects WHERE (xtype = 'v') AND (SUBSTRING(name, 1, 3) <> 'SYS') ";
			}
		}
		internal override string QUERY_TableColumn 
		{
			get 
			{
				return "SELECT     dbo.sysobjects.name AS Table_Name, dbo.syscolumns.name AS ColumnName, " + 
					"dbo.syscolumns.xtype AS TypeCode,dbo.syscolumns.length AS Length FROM dbo.sysobjects " +
					"INNER JOIN dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id " +
					"WHERE      (dbo.sysobjects.xtype = 'u' OR  dbo.sysobjects.xtype = 'v') ";
			}
		}
		#endregion

        protected override DataTable GetRelationInfo()
        {
            string connectionString = string.Format("Provider=SQLOLEDB;{0}"
                , m_sConnectionURL);


            DataTable dtRelationsInfo = null;
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                try
                {
                    dtRelationsInfo = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys,
                        new object[] { });
                }
                finally
                {
                    conn.Close();
                }
            }


            return dtRelationsInfo;

        }
		
	
        public override string DBNow
        {
            get { return "getDate()"; }
        }
		

		public SQLServerDataAccessor(string sAccessorName,string sConnectionURL):this(sAccessorName,sConnectionURL,true)
		{
		}

        public SQLServerDataAccessor(string sAccessorName, string sConnectionURL, bool validationRequired)
            : base(sAccessorName, sConnectionURL,validationRequired)
        {

        }




        internal override string AdjustUDType4BinaryField(int intTypeCode, string strUDType)
		{
			if(intTypeCode == 173 || intTypeCode == 165)strUDType="short" + strUDType;//173:binary  165:varbinary
			return strUDType;

		}
		
		
		
		public override string GetParameterName(string paramName)
		{
			return "@" + paramName;
		}
		public override IDbCommand CreateCommand()
		{
			
			return new SqlCommand(); 
		}

        internal override IDbDataAdapter CreateDataAdapter()
		{
			SqlDataAdapter adapter=new SqlDataAdapter();
			adapter.RowUpdating +=new SqlRowUpdatingEventHandler(Adapter_RowUpdating);
			adapter.RowUpdated +=new SqlRowUpdatedEventHandler(Adapter_RowUpdated);
			return  adapter;
		}

		public override IDataParameter GetParameter(string name,string type)
		{
			SqlParameter param=new SqlParameter(name,(SqlDbType)int.Parse(FreeHash.SQLDBType[type]),0); 
			return param;
		}
		

		
		protected override IDbConnection  CreateConnection()
		{
			SqlConnection conn=new SqlConnection(m_sConnectionURL);
			conn.StateChange +=new StateChangeEventHandler(base.Connection_StateChange);
			return conn;

		}

        private void Adapter_RowUpdating(object sender, SqlRowUpdatingEventArgs e)
        {
            base.Adapter_RowUpdating(sender, e);
            e.Command.CommandText = e.Command.CommandText.Replace(";", "--;");
            
        }

        private void Adapter_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
        {
            base.Adapter_RowUpdated(sender, e);

            
            
            e.Command.CommandText = e.Command.CommandText.Replace("--;", ";");
            SqlDataAdapter da = sender as SqlDataAdapter;
            if ((e.Command == da.UpdateCommand || e.Command == da.InsertCommand) &&
                e.Errors == null)
            {
                SqlCommand originalSelectCmd = da.SelectCommand;
                string originalCommandText = e.Command.CommandText;
                string[] currentSelectCommandsText = e.Command.CommandText.Split(';');

                if (currentSelectCommandsText.Length > 1
                    && currentSelectCommandsText[1].Trim() != string.Empty)
                {
                    e.Command.CommandText = currentSelectCommandsText[1];
                    try
                    {
                        using (SqlDataAdapter daTemp = new SqlDataAdapter())
                        {
                            daTemp.SelectCommand = e.Command;
                            using (DataTable dt = new DataTable())
                            {
                                daTemp.Fill(dt);
                                if (dt.Rows.Count > 0)
                                {
                                    DBUtil.CopyDataRow(dt.Rows[0], e.Row);
                                }
                            }
                        }
                    }
                    finally
                    {
                        da.SelectCommand = originalSelectCmd;
                        e.Command.CommandText = originalCommandText;
                    }
                }
            }
             
        }


        internal override string GeneratePagingQuery(string selectQuery, string sortedBy, bool isSortingTypeAscending, int pageNumber, int pageSize, long? totalRecords)
        {
			if(sortedBy.Trim()==string.Empty)
			{
				throw new Exception("[UC] Sorted Column should be defined");
			}

			string sortingType=(isSortingTypeAscending)? " ASC ": " DESC ";
			string inverseSortingType=(!isSortingTypeAscending)? " ASC ": " DESC ";
			string queryWithoutSelect = selectQuery.Substring(selectQuery.IndexOf("Select", StringComparison.InvariantCultureIgnoreCase) + "Select".Length);
			long noOfRequiredRows = pageNumber*pageSize;

            int numOfRowsNeedToReturn = pageSize;
            if (totalRecords.HasValue && totalRecords.Value < noOfRequiredRows)
            {
                //this is last page, it may cantains records less then of
                //page size.
                numOfRowsNeedToReturn = (noOfRequiredRows - totalRecords >= pageSize) ?
                     0 :
                    (int)(noOfRequiredRows - totalRecords) - pageSize; //(page rows not available) - page size

            }

			//query to fetch max required data for given critarea and order.
			string firstQuery = string.Format("Select Top {0} {1} order by {2} ",
                    noOfRequiredRows,queryWithoutSelect,
                    sortedBy.Replace(",", sortingType + ",") + sortingType);

			//query to get last n records of the result genrated by nested query [firstQuery] where n=pagesize.
			string secondQuery = string.Format("Select Top {0} * from ({1}) as firstQuery order by {2} ",
				pageSize, firstQuery, sortedBy.Replace("," , inverseSortingType + ",") + inverseSortingType);

			//order the result generated by nested query [secondQuery]    
			string thirdQuery = string.Format("Select Top {0} * from ({1}) as secondQuery order by {2} ",
				pageSize, secondQuery, sortedBy.Replace("," , sortingType + ",") + sortingType);

			return thirdQuery; 
		}
	}
}
